﻿---------------------------------------------------Creando la Base de Datos---------------------------------------------------
DROP DATABASE ReportesGerenciales;
GO

CREATE DATABASE ReportesGerenciales;
GO

USE ReportesGerenciales;
GO

------------------------------------Creando las tablas de la base de datos y sus relaciones------------------------------------
DROP TABLE TipoUsuario
GO

CREATE TABLE TipoUsuario(
	id_tipo numeric(2) identity(1,1),
	descripcion varchar(20),
	constraint pk_tipo_usuario primary key(id_tipo)
);
GO

/*Propiedades Extendidas (sirven para ver los comentarios de un campo en la base de datos) pues a veces hay campos que no se sabe para que sirven, 
es util agregar un comentario que explique para que sirve el campo, asi se puede ver posteriormente.
Sintaxis: 
EXECUTE sp_addextendedproperty 'MS_Description','descripcion del campo','USER','nombre esquema','TABLE','nombre tabla','COLUMN','nombre columna';*/

-- REHABILITAR
-- EXECUTE sp_addextendedproperty 'MS_Description','define el tipo de acceso al formulario','USER','dbo','TABLE','TipoUsuario','COLUMN','descripcion';

INSERT INTO TipoUsuario(descripcion) VALUES('admin');
INSERT INTO TipoUsuario(descripcion) VALUES('regular');
GO

----------------------------------------------------------------------------------------------------------------
DROP TABLE Usuario
GO

CREATE TABLE Usuario(
	id_usuario numeric(9) identity(1,1),
	usuario varchar(20) not null,
	pass varchar(40) not null,
	id_tipo_usuario numeric(2),
	constraint pk_usuario primary key(id_usuario)
);
GO

-- REHABILITAR
-- EXECUTE sp_addextendedproperty 'MS_Description','usuario para el login de la aplicacion','USER','dbo','TABLE','Usuario','COLUMN','usuario';
-- EXECUTE sp_addextendedproperty 'MS_Description','password para el login de la aplicacion','USER','dbo','TABLE','Usuario','COLUMN','pass';

INSERT INTO Usuario(usuario,pass,id_tipo_usuario) VALUES('master','942fc083c33225dac9ce13c6f9281fcd6af71214',1); --pass: meister
INSERT INTO Usuario(usuario,pass,id_tipo_usuario) VALUES('usuario','40bd001563085fc35165329ea1ff5c5ecbdbbeef',2); --pass: 123
GO

----------------------------------------------------------------------------------------------------------------
DROP TABLE Cheques;
GO

CREATE TABLE Cheques( --apchck
	no_vendedor varchar(10) NOT NULL, --cvendno
	beneficiario varchar(40) NOT NULL, --cpayto
	tipo_cheque char(1) NOT NULL, --cchktype
	cuenta_banco varchar(10) NOT NULL, --cbankno
	numero_cheque varchar(10) NOT NULL, --cchkno
	incluido_cierre_contable char(1) NOT NULL, --ctogl
	cheque_entregado char(1) NOT NULL,
	fecha_cheque datetime NOT NULL, --dcheck
	fecha_creacion_cheque datetime NOT NULL, --dcreate
	emitido_conciliacion_bancaria smallint NOT NULL, --lcancel
	retenido smallint NOT NULL, --lhold
	cantidad numeric(16,2) NOT NULL, --nchkamt
	cuenta_presupuestaria varchar(30),

	constraint pk_cheques primary key (numero_cheque),
);
GO

CREATE INDEX index_no_vendedor ON Cheques (no_vendedor);
CREATE INDEX index_beneficiario ON Cheques (beneficiario);
CREATE INDEX index_tipo_cheque ON Cheques (tipo_cheque);
CREATE INDEX index_cheque_entregado ON Cheques (cheque_entregado);
CREATE INDEX index_numero_cheque ON Cheques (numero_cheque);
GO


----------------------------------------------------------------------------------------------------------------
DROP TABLE Cargo;
GO

CREATE TABLE Cargo( --HRJobs
	id_cargo varchar(10) primary key, --cJobTitlNO
	descripcion varchar(40) --cDesc
);
GO

----------------------------------------------------------------------------------------------------------------
DROP TABLE Departamento;
GO

CREATE TABLE Departamento( --prdept
	id_departamento varchar(10) primary key, --cdeptno
	nombre varchar(30) --cdeptname
);
GO

----------------------------------------------------------------------------------------------------------------
DROP TABLE Empleado;
GO

CREATE TABLE Empleado( --prempy
	codigo_empleado varchar(10) primary key, --cempno
	identidad varchar(15), --cfedid
	nombres varchar(20), --cfname
	apellidos varchar(20), --clname
	codigo_departamento varchar(10), --cdeptno
	sueldo numeric(16,2), --nmonthpay
	fecha_contrato datetime, --dcntrct
	acuerdo datetime, --dhire
	fecha_nacimiento datetime, --dbirth
	impuesto_estatal char(2), --ctaxstate
	tipo_planilla char(2), --cpaytype
	estado char(2), --cstate
	sexo char(1), --csex
	id_cargo varchar(10) --cjobtitle
);
GO

----------------------------------------------------------------------------------------------------------------
DROP TABLE Cuentas_Empleado;
GO

CREATE TABLE Cuentas_Empleado( --Prempg
	codigo_empleado varchar(10), --cempno
	codigo_presupuestario varchar(30), --cwageacc
	constraint pk_cuentas_empleado primary key (codigo_empleado, codigo_presupuestario)
)
GO

----------------------------------------------------------------------------------------------------------------
DROP TABLE IngresoXEmpleado;
GO

CREATE TABLE IngresoXEmpleado( --prmisc
	codigo_empleado varchar(10), --cempno
	referencia varchar(54), --cref
	banco varchar(10), --cbankno
	cheque varchar(10), --cchkno
	planilla varchar(10), --cpayno
	codigo_ingreso varchar(10), --cpaycode
	cantidad numeric(16,4), --nqty
	tasa numeric(16,4) --nrate
);
GO

CREATE INDEX index_codigo_empleado ON IngresoXEmpleado (codigo_empleado);
CREATE INDEX index_cheque ON IngresoXEmpleado (cheque);
GO

----------------------------------------------------------------------------------------------------------------
DROP TABLE DeduccionXEmpleado;
GO

CREATE TABLE DeduccionXEmpleado( --Prmisd
	codigo_empleado varchar(10), --cempno
	id_deduccion varchar(15),
	planilla varchar(10), --cpayno
	codigo_ingreso_deduccion varchar(10), --cdedcode
	descripcion varchar(54), --cdesc
	valor_deducciones numeric(16,2) --ndedamt
);
GO

CREATE INDEX index_codigo_empleado ON DeduccionXEmpleado (codigo_empleado);
GO

----------------------------------------------------------------------------------------------------------------
DROP TABLE PresupuestoXPeriodo;
GO

CREATE TABLE PresupuestoXPeriodo( --Bgbudt
	codigo varchar(30), --cbudno (de aqui se obtiene el programa, subprograma, grupo, subgrupo y objeto de gasto)
	presupuesto varchar(10), --cbudid
	cantidad_modificada numeric(16,2), --namtmod
	cantidad numeric(16,2), --namt
	cantidad_usada numeric(16,2) --namtused	
)
GO

CREATE INDEX index_codigo ON PresupuestoXPeriodo (codigo);
CREATE INDEX index_presupuesto ON PresupuestoXPeriodo (presupuesto);
GO

----------------------------------------------------------------------------------------------------------------
DROP TABLE Banco;
GO

CREATE TABLE Banco(
	id_banco numeric(11) NOT NULL IDENTITY(1,1),
	nombre_banco varchar(30),
	CONSTRAINT pk_banco PRIMARY KEY(id_banco)
);
GO

----------------------------------------------------------------------------------------------------------------
DROP TABLE Cuenta_Banco;
GO

CREATE TABLE Cuenta_Banco(
	cuenta_banco varchar(10) NOT NULL,
	id_banco numeric(11) NOT NULL,
	saldo_inicial numeric(16,2),
	saldo numeric(16,2),
	CONSTRAINT pk_cuentabanco PRIMARY KEY(cuenta_banco),
	CONSTRAINT fk_cuentabanco_banco FOREIGN KEY(id_banco) REFERENCES Banco(id_banco)
);
GO




---------------------------------------------------Creando Usuario "master"---------------------------------------------------
/*Creacion de un usuario se ejecuta la funcion sp_addlogin, la propiedad @loginame es el nombre de usuario, @passwd es la contraseña,
@defdb es la base de datos por default que se le asigna al usuario
A parte de crear el usuario para cada base tambien se debe crear el usuario:
La funcion sp_adduser agrega un usuario existente a la base de datos seleccionada, @loginame el nombre de usuario, 
@name_in_db es el sobrenombre o alias en la base*/

-- REHABILITAR
-- USE master
-- exec sp_addlogin @loginame='master',@passwd='meister',@defdb='ReportesGerenciales';
-- USE ReportesGerenciales
-- exec sp_adduser @loginame='master',@name_in_db='master';

----------------------------------------Otorgando o quitando permisos al usuario "master"----------------------------------------
/*Para otorgar permisos se utiliza la clausula GRANT y REVOKE para quitar un permiso, se puede usar multiples acciones, 
Para funciones y procedimientos almacenados se utiliza la clausula EXECUTE
Al final se proporcionoa a que usuario le pertenece con To <user> , en donde user es el usuario a quien le hara efecto
Ejemplos:
GRANT SELECT,INSERT,UPDATE,DELETE ON Usuario TO master //otorga los permisos de select,insert,update y delete al usuario master
REVOKE SELECT,INSERT,UPDATE,DELETE ON Usuario TO master //quita los permisos de select,insert,update y delete al usuario master
GRANT EXECUTE ON ValidarLogin TO master //otorga permiso de ejecutar funcion ValidarLogin al usuario master
REVOKE EXECUTE ON ValidarLogin TO master //quita permiso de ejecutar funcion ValidarLogin al usuario master*/

-- REHABILITAR
-- REVOKE SELECT,INSERT,UPDATE,DELETE ON Usuario TO master
-- REVOKE SELECT,INSERT,UPDATE,DELETE ON TipoUsuario TO master
-- REVOKE SELECT,INSERT,UPDATE,DELETE ON TipoUsuario TO master
-- REVOKE SELECT,INSERT,UPDATE,DELETE ON Cheques TO master


-- GRANT SELECT ON ListarUsuarios TO master
-- GRANT EXECUTE ON ValidarLogin TO master
-- GRANT EXECUTE ON InsertarUsuario TO master
-- GRANT EXECUTE ON EliminarUsuario TO master
-- GRANT EXECUTE ON ActualizarUsuario TO master

---------------------------------------------------Creando usuario "usuario"---------------------------------------------------
-- REHABILITAR
-- exec sp_addlogin @loginame='usuario',@passwd='123',@defdb='ReportesGerenciales';
-- USE ReportesGerenciales
-- exec sp_adduser @loginame='usuario',@name_in_db='usuario';
----------------------------------------Otorgando o quitando permisos al usuario "usuario"----------------------------------------
-- REVOKE SELECT,INSERT,UPDATE,DELETE ON Usuario TO usuario
-- REVOKE SELECT,INSERT,UPDATE,DELETE ON TipoUsuario TO usuario
-- REVOKE SELECT,INSERT,UPDATE,DELETE ON TipoUsuario TO usuario
-- REVOKE SELECT,INSERT,UPDATE,DELETE ON Cheques TO usuario


-- GRANT SELECT ON ListarUsuarios TO usuario
-- GRANT EXECUTE ON ValidarLogin TO usuario
-- REVOKE EXECUTE ON InsertarUsuario TO usuario
-- REVOKE EXECUTE ON EliminarUsuario TO master
-- REVOKE EXECUTE ON ActualizarUsuario TO master
